﻿using System;
using System.Collections.Generic; 
using System.Text;
using Runda.CMS.Common.Entity;
using Runda.CMS.Common.Utility;
using System.Data.Common;
using System.Data;

namespace Runda.CMS.Dal
{
    public partial class CtrtInstrumentDal
    {
        /// <summary>
        /// Get Instrument By Category
        /// </summary>
        /// <param name="CategoryID"></param>
        /// <returns></returns>
        public List<CtrtInstrumentEntity> GetInstrumentEntities(Guid CategoryID)
        {
            List<CtrtInstrumentEntity> entities = null;
            string sql = @"with CTE as
(
select * from Cms_Ctrt_InstrumentCategory where categoryGUID = @CategoryID

union all

select A.* from Cms_Ctrt_InstrumentCategory A join CTE on A.parentGUID = CTE.categoryGUID
)

select * from Cms_Ctrt_Instrument where categoryGUID in (select categoryGUID from  CTE )";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@CategoryID", System.Data.DbType.Guid, CategoryID);
            using (IDataReader reader = CurrentDatabase.ExecuteReader(dbcommand))
            {
                entities = GetListFromReader<CtrtInstrumentEntity>(reader);
            }
            return entities;
        }

        public DataTable GetInstrumentEntities(Guid CategoryID,string CustomerCode)
        { 
            string sql = @"with CTE as
(
select * from Cms_Ctrt_InstrumentCategory where categoryGUID = @CategoryID

union all

select A.* from Cms_Ctrt_InstrumentCategory A join CTE on A.parentGUID = CTE.categoryGUID
)

select A.instrumentGUID,A.[Type],A.InstrumentCode,A.InstrumentName,A.InstrumentStandard,A.[unit],A.vendor,
case A.[type] when 1 then C.supplyprice else A.PresellPrice end as PresellPrice,B.CustomerName 
from Cms_Ctrt_Instrument A 
left join Cms_Ctrt_Customer B on  B.CustomerCode = @CustomerCode
left join Cms_Ctrt_CustomeInstrumentPrice C on A.InstrumentCode = C.InstrumentCode and C.CustomerCode = @CustomerCode
where A.categoryGUID in (select categoryGUID from  CTE )";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@CategoryID", System.Data.DbType.Guid, CategoryID);
            CurrentDatabase.AddInParameter(dbcommand, "@CustomerCode", System.Data.DbType.String, CustomerCode);
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
         
        }


        public List<CtrtInstrumentEntity> GetInstrumentEntities(string Code)
        {
            List<CtrtInstrumentEntity> entities = null;
            string sql = @"select * from Cms_Ctrt_Instrument where 
instrumentCode like @code
or instrumentName like @code
or categoryGUID in (select categoryGUID from Cms_Ctrt_InstrumentCategory where categoryCode like @code)
";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@code", System.Data.DbType.String, "%"+Code+"%");
            using (IDataReader reader = CurrentDatabase.ExecuteReader(dbcommand))
            {
                entities = GetListFromReader<CtrtInstrumentEntity>(reader);
            }
            return entities;
        }

        public DataTable GetInstrumentEntities(string Code, string CustomerCode)
        {
            string sql = @"select A.instrumentGUID,A.[Type],A.InstrumentCode,A.InstrumentName,A.InstrumentStandard,A.[unit],A.vendor,
case A.[type] when 1 then C.supplyprice else A.PresellPrice end as PresellPrice,B.CustomerName 
from Cms_Ctrt_Instrument A 
left join Cms_Ctrt_Customer B on  B.CustomerCode = @CustomerCode
left join Cms_Ctrt_CustomeInstrumentPrice C on A.InstrumentCode = C.InstrumentCode and C.CustomerCode = @CustomerCode
where  A.instrumentCode like @code
or A.instrumentName like @code
or A.categoryGUID in (select categoryGUID from Cms_Ctrt_InstrumentCategory where categoryCode like @code)
";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@code", System.Data.DbType.String, "%"+Code+"%");
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];          
        }

        public void SynchroInstrument()
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("SyncInstrumentCategory");

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }
    }
}
